package com.hongye.app.model;

import java.util.List;

import com.hongye.app.controller.UserAppInfoController;
import com.hongye.util.Utils;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Page;

public class OrderInfoModel extends Model<OrderInfoModel>{

	private static final long serialVersionUID = -2189262400221456792L;
	
	public static final OrderInfoModel dao=new OrderInfoModel();
	
	/**
	 * 后台管理，查询订单-分页
	 * @author liushaomin
	 * 2016年10月6日
	 * @param pageNumber
	 * @return
	 */
	public Page<OrderInfoModel> ordersReview(int pageNumber){
		String sql="SELECT * ";
		Page<OrderInfoModel> orderlist = dao.paginate(pageNumber, Utils.pageSize, sql,"FROM order_info oi , order_goods og, buyer b WHERE oi.`id` = og.`order_id` AND og.`seller_id` = b.`user_id`");
		return orderlist;
	}
	
	/**
	 * 查询订单--后台管理
	 * @author lifuqiang
	 * 2016-12-22
	 * @return
	 */
	public List<OrderInfoModel> getAllOrder(){
		String sql="SELECT * from order_info where type = 0 order by c_time desc";
		List<OrderInfoModel> orderinfo_list = dao.find(sql);
		for (OrderInfoModel orderinfo_item : orderinfo_list) {
			getOrderGoods_Admin(orderinfo_item);
		}
		return orderinfo_list;
	}
	
	/**
	 * 筛选订单信息,时间查询--后台专用 
	 * @author liushaomin
	 * 2017年7月19日
	 * @param time_start
	 * @param time_end
	 * @param state
	 * @return
	 */
	public static List<OrderInfoModel> getOrder_Admin(String time_start, String time_end, String state){
		String sql = "select * from order_info where type = 0 and c_time >= '"+time_start+"' and c_time <= '"+time_end+"' and order_status in ( "+state+" ) order by c_time desc";
		List<OrderInfoModel> orderinfo_list = dao.find(sql);
		for (OrderInfoModel orderinfo_item : orderinfo_list) {
			getOrderGoods_Admin(orderinfo_item);
		}
		return orderinfo_list;
	}
	
	/**
	 * 获取订单的对应的商品--后台专用
	 * @author liushaomin
	 * 2017年7月19日
	 * @param orderinfo
	 */
	public static void getOrderGoods_Admin(OrderInfoModel orderinfo){
		String sql_ordergoods = "select * from order_goods where order_id = "+ orderinfo.get("id") +"";
		orderinfo.put("ordergoods", OrderGoodsModel.dao.find(sql_ordergoods));
	}
	
	/**
	 * 得到订单的商家商家手机
	 * @author liushaomin
	 * 2017年7月18日
	 * @param order_sn
	 * @return
	 */
	public static String getSellerByOrdersn(String order_sn){
		UserAppModel user = UserAppModel.dao.findById(getOrderSeller_id(order_sn));
		return user.getStr("phone");
	}
	
	/**
	 * 得到订单的商家的id
	 * 允许oi.id = og.order_id
	 * @author liushaomin
	 * 2017年7月18日
	 * @param ordersn 订单号
	 * @return
	 */
	public static int getOrderSeller_id(String ordersn) {
		String sql="select og.seller_id from order_info oi, order_goods og where oi.order_sn = '"+ordersn+"' and oi.id = og.order_id";
		OrderInfoModel order = dao.findFirst(sql);
		return order.getInt("seller_id");
	}
	
	/**
	 * 得到订单的商家的id
	 * @author liushaomin
	 * 2017年7月18日
	 * @param orderid 订单id
	 * @return
	 */
	public static int getOrderSeller_id(int orderid) {
		String sql="select seller_id from order_info where order_id = "+ orderid +"";
		OrderGoodsModel ordergoods = OrderGoodsModel.dao.findFirst(sql);
		return ordergoods.getInt("seller_id");
	}
	
	/**
	 * 根据订单号获取订单 订单的全部信息(不包括商品信息)
	 * @author liushaomin
	 * 2017年4月19日
	 * @param ordersn
	 * @return
	 */
	public static OrderInfoModel getOrderBySN(String ordersn) {
		String sql = "select * from order_info where order_sn = '"+ordersn+"'";
		OrderInfoModel order = dao.findFirst(sql);
		return order;
	}
	
	/**
	 * 根据订单id获取订单 订单的全部信息(包括商品信息)
	 * @author liushaomin
	 * 2017年7月19日
	 * @param orderid
	 * @return
	 */
	public static OrderInfoModel getOrderById(String orderid) {
		String sql = "select * from order_info where id = "+ orderid +"";
		OrderInfoModel order = dao.findFirst(sql);
		getOrderGoods_Admin(order);
		return order;
	}
	
	/**
	 * 订单列表显示 买家
	 * 根据手机号获取用户的状态为state的订单
	 * @author liushaomin
	 * 2017年7月14日
	 * @param phone
	 * @param state
	 * @return
	 */
	public static List<OrderInfoModel> getNoPayOrder(String phone, String state) {
		String sql = "select id, order_sn, postage_typeName, total_price, c_time from order_info where phone = '"+phone+"' and order_status = "+ state +" order by c_time desc";
		List<OrderInfoModel> orderlist = dao.find(sql);
		for (OrderInfoModel item : orderlist) {
			getOrderGoodsByOrderList(item);
		}
		return orderlist;
	}
	
	/**
	 * 买家订单列表--已分页处理
	 * @author liushaomin
	 * 2017年7月14日
	 * @param phone
	 * @param state
	 * @param pageNumber
	 * @return
	 */
	public static Page<OrderInfoModel> getOrderListByStatus(String phone, String state, int pageNumber){
		String sql="select id, order_sn, postage_typeName, total_price, c_time, express, waybillnumber, order_status";
		String str = "from order_info where phone='"+ phone +"' and order_status in ("+state+") and del = 0 order by c_time desc";
		Page<OrderInfoModel> orderlist = dao.paginate(pageNumber, Utils.pageSize, sql,str);
		for (OrderInfoModel orderitem : orderlist.getList()) {
			orderitem.put("return_id", null);
			if(orderitem.getInt("order_status")==10){
				orderitem.put("return_id", ReturnGoodsModel.getByG_order(orderitem.getStr("order_sn"), 1).getInt("return_id"));
			}
//			String sql_ordergoods = "select goods_id, goods_name, goods_price, goods_num, goods_thumb, seller_id from order_goods where order_id = "+ orderitem.get("id") +"";
//			List<OrderGoodsModel> order_goodslist =  OrderGoodsModel.dao.find(sql_ordergoods);
//			for (OrderGoodsModel ordergoods_item : order_goodslist) {
//				ordergoods_item.put("goods_thumb", UserAppInfoController.ImgPath + ordergoods_item.getStr("goods_thumb"));
//			}
//			orderitem.put("order_goodslist", order_goodslist);
//			String seller_id = order_goodslist.get(0).getInt("seller_id").toString();
//			BuyerStatisticsModel buyersta = BuyerStatisticsModel.getStatisticsByID(seller_id);
//			orderitem.put("shopname", buyersta.getStr("shop_name"));
//			orderitem.put("seller_phone", buyersta.getStr("buyer_phone"));
			getOrderGoodsByOrderList(orderitem);
		}
		return orderlist;
	}
	
	/**
	 * 设置orderlist中ordergoods信息
	 * @author liushaomin
	 * 2017年7月19日
	 * @param orderlist
	 */
	public static void getOrderGoodsByOrderList(OrderInfoModel orderinfo){
		String sql_ordergoods = "select goods_id, goods_name, goods_price, goods_num, goods_thumb, seller_id from order_goods where order_id = "+ orderinfo.get("id") +"";
		List<OrderGoodsModel> order_goodslist =  OrderGoodsModel.dao.find(sql_ordergoods);
		for (OrderGoodsModel ordergoods_item : order_goodslist) {
			ordergoods_item.put("goods_thumb", UserAppInfoController.ImgPath + ordergoods_item.getStr("goods_thumb"));
		}
		orderinfo.put("order_goodslist", order_goodslist);
		String seller_id = order_goodslist.get(0).getInt("seller_id").toString();
		BuyerStatisticsModel buyersta = BuyerStatisticsModel.getStatisticsByID(seller_id);
		orderinfo.put("shopname", buyersta.getStr("shop_name"));
		orderinfo.put("seller_phone", buyersta.getStr("buyer_phone"));
	}
	
	/**
	 *  卖家（商家）订单列表--已分页处理
	 * @author liushaomin
	 * 2017年7月18日
	 * @param phone
	 * @param state
	 * @param pageNumber
	 * @return
	 */
	public static Page<OrderInfoModel> getOrderlistBySeller(String phone, String state, int pageNumber){
		String sql="select id, order_sn, postage_typeName, del, express, waybillnumber, c_time, total_price, order_status, c_time";
		String str = "from order_info where order_status in ("+state+") and id in( select og.order_id from buyer b, order_goods og where b.phone = '"+phone+"' and b.user_id = og.seller_id group by order_id ) order by update_time desc";
		Page<OrderInfoModel> order_list = dao.paginate(pageNumber, Utils.pageSize, sql, str);
		for (OrderInfoModel info : order_list.getList()) {
			String sql_ordergoods = "select goods_id, goods_name, goods_price, goods_num, goods_thumb from order_goods where order_id = "+ info.get("id") +"";
			List<OrderGoodsModel> order_goodslist =  OrderGoodsModel.dao.find(sql_ordergoods);
			for (OrderGoodsModel ordergoods_item : order_goodslist) {
				ordergoods_item.put("goods_thumb", UserAppInfoController.ImgPath + ordergoods_item.getStr("goods_thumb"));
			}
			info.put("goodslist", order_goodslist);
			info.put("return_id", null);
			if(info.getInt("order_status")==10){
				info.put("return_id", ReturnGoodsModel.getByG_order(info.getStr("order_sn"), 1).get("return_id"));
			}
		}
		return order_list;
	}
	
	/**
	 * 订单状态更新为已付款，等待发货
	 * 更新支付渠道、交易号、订单状态、支付时间
	 * @author liushaomin
	 * 2016年12月6日
	 * @param ordersn
	 * @param payment_channel 0支付宝 1微信
	 * @param trade_num 交易号
	 * @return
	 */
	public static boolean UpdateStatusByOrdersn(String ordersn, String payment_channel, String trade_num) {
		OrderInfoModel orderinfo = getOrderBySN(ordersn);
		boolean boo = orderinfo.set("order_status", 2).set("payment_channel", payment_channel).set("trade_num", trade_num).set("pay_time", Utils.getDate()).update();
		if(boo){
		}
		BuyerStatisticsModel.Update_Add_Disable(orderinfo);
		return boo;
	}
	
	/**
	 * 订单状态修改为 已退款
	 * @author liushaomin
	 * 2017年2月9日
	 * @param order
	 * @param status 更新后的状态
	 * @return
	 */
	public static boolean UpdateOrderStatus(OrderInfoModel order, int status) {
		boolean boo = order.set("order_status", status).set("update_time", Utils.getDate()).update();
		return boo;
	}
	
	/**
	 * 定时器--取消订单获取订单list
	 * @author liushaomin
	 * 2017年7月20日
	 * @return
	 */
	public List<OrderInfoModel> cancelOrder(){
		String sql = "select order_sn, id, phone, c_time as time from order_info where isdelay = 0 and order_status in (0,1) order by time desc";
		List<OrderInfoModel> order_list = OrderInfoModel.dao.find(sql);
		return order_list;
	}
	
	/**
	 * 定时器--确定收货获取订单list
	 * @author lifuqiang
	 * 2016-12-19
	 * @return
	 */
	public List<OrderInfoModel> confirmReceipt(){
		String sql = "select order_sn, id, bill_time as time from order_info where order_status = 3 and  isdelay = 0 order by time desc";//正常待收货的订单（未被延期的）
		List<OrderInfoModel> order_list = OrderInfoModel.dao.find(sql);
		return order_list;
	}
	
	/**
	 * 定时器--延长收货订单，获取自动确定收货订单list
	 * @author lifuqiang
	 * 2016-12-19
	 * @return
	 */
	public List<OrderInfoModel> delayOrder(){
		String sql = "select order_sn, id, bill_time as time from order_info where order_status = 3 and isdelay = 1 order by time desc";//延期收货待收货的订单
		List<OrderInfoModel> order_list = OrderInfoModel.dao.find(sql);
		return order_list;
	}
	
	/**
	 * 定时器--获取待评价的订单list
	 * @author lifuqiang
	 * 2016-12-19
	 * @return
	 */
	public List<OrderInfoModel> evaluateOrder(){
		String sql = "select order_sn, id, bill_time as time from order_info where order_status = 5 order by time desc";//所有收货确定收货的订单
		List<OrderInfoModel> order_list = OrderInfoModel.dao.find(sql);
		return order_list;
	}

	/**
	 * 得到订单集合的价格综合
	 * @author liushaomin
	 * 2016年12月23日
	 * @param orderids 订单id的数组[1,2,3]
	 * @return
	 */
	public double getTotal_PriceByID(String orderids) {
		String sql = "select total_price from order_info where id in ("+ orderids +")";
		double summoney = 0;
		List<OrderInfoModel> orders = OrderInfoModel.dao.find(sql);
		for (OrderInfoModel item : orders) {
			summoney = Utils.add(summoney, Double.valueOf(item.get("total_price").toString()));
		}
		return summoney;
	}
	
	 /**
	  * 根据订单 id 数组  得到list<orderinfo>
	  * @author liushaomin
	  * 2017年7月18日
	  * @param orderids 订单id 逗号分割
	  * @return
	  */
	public static List<OrderInfoModel> getOrderByINOrderids(String orderids) {
		String sql_orderinfo = "select * from order_info where id in ("+orderids+")";
		List<OrderInfoModel> orders = OrderInfoModel.dao.find(sql_orderinfo);
		return orders;
	}
	
	/**
	 * 判断该订单是否存在总订单 返回订单对象
	 * @author lifuqiang
	 * 2017-2-17
	 * @param orderid
	 * @return
	 */
	public static OrderInfoModel isTotalOrder(Object orderid){
		return OrderInfoModel.dao.findFirst("select * from order_info where FIND_IN_SET("+orderid+", orderids)");
	}
	
	/**
	 * 作为买家
	 * 获取某人某状态的订单数量
	 * @author liushaomin
	 * 2017年2月24日
	 * @param phone
	 * @param instatus
	 * @return
	 */
	public static Long queryIntByBuyer(String phone, String instatus){
		return Db.queryLong("select count(1) from order_info where order_status in(" + instatus + ") and phone =" + phone + "");
	} 
	
	/**
	 * 作为商家
	 * 获取某人某状态的订单数量
	 * @author liushaomin
	 * 2017年2月24日
	 * @param phone
	 * @param instatus
	 * @return  sql语句去重
	 */
	public static Long queryIntBySeller(String phone, String instatus){
		//"SELECT COUNT(1) FROM ORDER_INFO OI, ORDER_GOODS OG, BUYER B WHERE B.PHONE = '"+PHONE+"' AND OI.ID = OG.ORDER_ID AND B.USER_ID = OG.SELLER_ID AND OI.ORDER_STATUS IN("+INSTATUS+")"
		String sql = "select count(1) from order_info where order_status in ("+instatus+") and id in ( select og.order_id from buyer b, order_goods og where b.phone = '"+phone+"' and b.user_id = og.seller_id group by order_id)";
		return Db.queryLong(sql);
	}

	/**
	 * 买家退货列表
	 * 根据订单号获取订单基本信息
	 * @author liushaomin
	 * 2017年7月19日
	 * @param order_sn
	 * @return
	 */
	public static OrderInfoModel getOrder_byOrdersn_Buyer(String order_sn){
		String sql = "select id, order_sn, postage_typeName, total_price, c_time, order_status from order_info where order_sn = '"+order_sn+"' and del = 0";
		OrderInfoModel info = OrderInfoModel.dao.findFirst(sql);
		getOrderGoodsByOrderList(info);
		return info;
	}
	
	/**
	 * 商家退货列表
	 * 根据订单号获取订单基本信息
	 * @author liushaomin
	 * 2017年7月21日
	 * @param order_sn
	 * @return
	 */
	public static OrderInfoModel getOrder_byOrdersn_Seller(String order_sn){
		String sql = "select id, order_sn, postage_typeName, total_price, c_time, order_status from order_info where order_sn = '"+order_sn+"'";
		OrderInfoModel info = OrderInfoModel.dao.findFirst(sql);
		getOrderGoodsByOrderList(info);
		return info;
	}
	
	/**
	 * 订单交易量
	 * @author lifuqiang
	 * 2017-4-20
	 * @return
	 */
	public static long orderNumber(){
		String sql = "select * from order_info where order_status in (2,3,4,5,6,7,8,9,10,11)";
		List<OrderInfoModel> list = dao.find(sql);
		return list.size();
	}
	
	/**
	 * 总订单交易额（支付就算）
	 * @author lifuqiang
	 * 2017-4-20
	 * @return
	 */
	public static double moneynumber(){
		String sql = "SELECT ROUND(SUM(total_price) ,2) AS totalprice FROM order_info  WHERE order_status <> 0 AND order_status <> 1 AND order_status <> 6";
		OrderInfoModel order = dao.findFirst(sql);
		if(order.get("totalprice") != null){
			return order.getDouble("totalprice");
		}
		return 0.0;
	}

}
